﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using Estimate.Common.DBFieldTypes;


namespace Estimate.Common.Repository
{
    /// <summary>
    /// 封闭数据库中对 StationMatch表的操作
    /// </summary>
    public class StationMatchRepository : BaseRepository
    {
        public StationMatchRepository(string connectionString):base(connectionString,"StationMatch")
        {
            
        }

        public override string GetCreateTableSql()
        {
            string sqlcreate = String.Format("CREATE TABLE [{0}](id bigint identity(1,1), name varchar(20),type varchar(8),extral varchar(8),stations text) \n", _tableName);
            sqlcreate += String.Format("CREATE CLUSTERED INDEX {0}_INDEX ON {0} (name,type,extral) \n", _tableName, _tableName);
            sqlcreate += String.Format("alter table {0} add primary key (id)", _tableName);
            return sqlcreate;
        }

        public override void AfterCreateTable()
        {
            InsertRecord("南京",StationMatchStationsType.area,StationMatchExtralType.cz,"58238;58237;58235;58340;58339;58333");
            InsertRecord("徐州", StationMatchStationsType.area, StationMatchExtralType.cz, "58027;58012;58013;58026;58035;58130");
            InsertRecord("连云港", StationMatchStationsType.area, StationMatchExtralType.cz, "58044;58040;58036;58047;58048");
            InsertRecord("盐城", StationMatchStationsType.area, StationMatchExtralType.cz, "58151;58045;58049;58143;58146;58150;58158;58251");
            InsertRecord("宿迁", StationMatchStationsType.area, StationMatchExtralType.cz, "58131;58038;58132;58135");
            InsertRecord("淮安", StationMatchStationsType.area, StationMatchExtralType.cz, "58141;58138;58139;58140;58145;58147");
            InsertRecord("扬州", StationMatchStationsType.area, StationMatchExtralType.cz, "58245;58148;58241;58244;58242");
            InsertRecord("泰州", StationMatchStationsType.area, StationMatchExtralType.cz, "58246;58257;58249;58250;58243");
            InsertRecord("南通", StationMatchStationsType.area, StationMatchExtralType.cz, "58259;58254;58255;58264;58268;58360;58269");
            InsertRecord("镇江", StationMatchStationsType.area, StationMatchExtralType.cz, "58248;58341;58344;58247");
            InsertRecord("常州", StationMatchStationsType.area, StationMatchExtralType.cz, "58343;58345;58342");
            InsertRecord("无锡", StationMatchStationsType.area, StationMatchExtralType.cz, "58354;58351;58346");
            InsertRecord("苏州", StationMatchStationsType.area, StationMatchExtralType.cz, "58357;58353;58352;58356;58377;58359");

            InsertRecord("南京", StationMatchStationsType.city, StationMatchExtralType.cz, "58238");
            InsertRecord("徐州", StationMatchStationsType.city, StationMatchExtralType.cz, "58027");
            InsertRecord("连云港", StationMatchStationsType.city, StationMatchExtralType.cz,"58044");
            InsertRecord("盐城", StationMatchStationsType.city, StationMatchExtralType.cz, "58151");
            InsertRecord("宿迁", StationMatchStationsType.city, StationMatchExtralType.cz, "58131");
            InsertRecord("淮安", StationMatchStationsType.city, StationMatchExtralType.cz, "58141");
            InsertRecord("扬州", StationMatchStationsType.city, StationMatchExtralType.cz, "58245");
            InsertRecord("泰州", StationMatchStationsType.city, StationMatchExtralType.cz, "58246");
            InsertRecord("南通", StationMatchStationsType.city, StationMatchExtralType.cz, "58259");
            InsertRecord("镇江", StationMatchStationsType.city, StationMatchExtralType.cz, "58248");
            InsertRecord("常州", StationMatchStationsType.city, StationMatchExtralType.cz, "58343");
            InsertRecord("无锡", StationMatchStationsType.city, StationMatchExtralType.cz, "58354");
            InsertRecord("苏州", StationMatchStationsType.city, StationMatchExtralType.cz, "58357");

            InsertRecord("南京",StationMatchStationsType.area,StationMatchExtralType.zd,"58238;58237;58235;58340;58339;58333");
            InsertRecord("徐州", StationMatchStationsType.area, StationMatchExtralType.zd, "58027;58012;58013;58026;58035;58130");
            InsertRecord("连云港", StationMatchStationsType.area, StationMatchExtralType.zd, "58044;58040;58036;58047;58048");
            //指导站 盐城 与 城镇 不一样
            InsertRecord("盐城", StationMatchStationsType.area, StationMatchExtralType.zd, "58154;58045;58049;58143;58146;58150;58158;58251");
            InsertRecord("宿迁", StationMatchStationsType.area, StationMatchExtralType.zd, "58131;58038;58132;58135");
            InsertRecord("淮安", StationMatchStationsType.area, StationMatchExtralType.zd, "58141;58138;58139;58140;58145;58147");
            InsertRecord("扬州", StationMatchStationsType.area, StationMatchExtralType.zd, "58245;58148;58241;58244;58242");
            InsertRecord("泰州", StationMatchStationsType.area, StationMatchExtralType.zd, "58246;58257;58249;58250;58243");
            InsertRecord("南通", StationMatchStationsType.area, StationMatchExtralType.zd, "58259;58254;58255;58264;58268;58360;58269");
            //指导站 镇江 与 城镇 不一样
            InsertRecord("镇江", StationMatchStationsType.area, StationMatchExtralType.zd, "58252;58341;58344;58247");
            InsertRecord("常州", StationMatchStationsType.area, StationMatchExtralType.zd, "58343;58345;58342");
            InsertRecord("无锡", StationMatchStationsType.area, StationMatchExtralType.zd, "58354;58351;58346");
            InsertRecord("苏州", StationMatchStationsType.area, StationMatchExtralType.zd, "58357;58353;58352;58356;58377;58359");

            InsertRecord("ST", StationMatchStationsType.st, StationMatchExtralType.cz, "58238;58237;58235;58340;58339;58333;58027;58012;58013;58026;58035;58130;58044;58040;58036;58047;58048;58151;58045;58049;58143;58146;58150;58158;58251;58131;58038;58132;58135;58141;58138;58139;58140;58145;58147;58245;58148;58241;58244;58242;58246;58257;58249;58250;58243;58259;58254;58255;58264;58268;58360;58269;58248;58341;58344;58247;58343;58345;58342;58354;58351;58346;58357;58353;58352;58356;58377;58359");
            InsertRecord("ST", StationMatchStationsType.st, StationMatchExtralType.zd, "58238;58237;58235;58340;58339;58333;58027;58012;58013;58026;58035;58130;58044;58040;58036;58047;58048;58151;58045;58049;58143;58146;58150;58158;58251;58131;58038;58132;58135;58141;58138;58139;58140;58145;58147;58245;58148;58241;58244;58242;58246;58257;58249;58250;58243;58259;58254;58255;58264;58268;58360;58269;58248;58341;58344;58247;58343;58345;58342;58354;58351;58346;58357;58353;58352;58356;58377;58359");                                                        
        }

        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="name">名称</param>
        /// <param name="type">单市站、区域或站点</param>
        /// <param name="extral">城镇或指导</param>
        /// <param name="stations">站点列表</param>
        public void InsertRecord(string name, StationMatchStationsType type, StationMatchExtralType extral, string stations)
        {
            string sql = String.Format("INSERT INTO [{0}] (name,type,extral,stations) values(@name,@type,@extral,@stations)", _tableName);
            SqlParameter[] param = new SqlParameter[4];
            param[0] = new SqlParameter("@name", name);
            param[1] = new SqlParameter("@type", type.ToString());
            param[2] = new SqlParameter("@extral", extral.ToString());
            param[3] = new SqlParameter("@stations", stations);
            DBHelper.ExecuteSql(_connectionString, sql, param);
        }

        
        /// <summary>
        /// 给定参数，或者站点字符串
        /// </summary>
        /// <param name="name">城市名称</param>
        /// <param name="type">单市站、区域或站点</param>
        /// <param name="extral">城镇或指导</param>
        /// <returns>站点列表</returns>
        public string GetStations(string name, StationMatchStationsType type, StationMatchExtralType extral )
        {
        //     CacheClient.KeyPrefix = this._tableName + "GetStations";
        //    List<object> ps = new List<object>() { type, name, extral };           
        //    string cacheKey = getValideCacheKey(ps);
           
        //    object cache = CacheClient.Get(cacheKey);
        //    if (cache == null)
        //    {
                string sql = String.Format("SELECT stations FROM [{0}] WHERE name=@name and type=@type and extral=@extral", _tableName);
                SqlParameter[] param = new SqlParameter[3];
                param[0] = new SqlParameter("@name", name);
                param[1] = new SqlParameter("@type", type.ToString());
                param[2] = new SqlParameter("@extral", extral.ToString());

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);

                string result = "";
                if (reader.Read())
                {
                    result = reader["stations"].ToString();
                    connection.Close();

                }
                //CacheClient.Set(cacheKey, result);
                return result;
            //}
            //else
            //{
            //    string result = cache as string;
            //    return result;
            //}
        }

        /// <summary>
        /// 获取数据库中某种类型地区的名称列表
        /// </summary>
        /// <param name="type">单市站、区域或站点</param>
        /// <param name="extral">城镇或指导</param>
        /// <returns>站点名称列表</returns>
        public string[] GetAllNames(StationMatchStationsType type, StationMatchExtralType extral)
        {
            CacheClient.KeyPrefix = this._tableName + "GetAllNames";
            List<object> ps = new List<object>() { type, extral };
            string cacheKey = getValideCacheKey(ps);

            object cache = CacheClient.Get(cacheKey);

            if (cache == null)
            {
                string sql = String.Format("SELECT name FROM [{0}] WHERE  type=@type and extral=@extral", _tableName);
                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@type", type.ToString());
                param[1] = new SqlParameter("@extral", extral.ToString());

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);
                List<string> result = new List<string>();
                while (reader.Read())
                {
                    result.Add(reader["name"].ToString());
                }
                connection.Close();
                CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                string[] result = cache as string[];
                return result;
            }
        }

        /// <summary>
        /// 获取数据库中某种类型地区的名称列表
        /// </summary>
        /// <param name="type">单市站、区域或站点</param>
        /// <param name="extral">城镇或指导</param>
        /// <returns>站点名称列表</returns>
        public Dictionary<string,string> GetAllNameStationDictionary(StationMatchStationsType type, StationMatchExtralType extral)
        {
            //CacheClient.KeyPrefix = this._tableName + "GetAllNameStationDictionary";
            //List<object> ps = new List<object>() { type, extral };           
            //string cacheKey = getValideCacheKey(ps);
           
            //object cache = CacheClient.Get(cacheKey);

            //if (cache == null)
            //{

                string sql = String.Format("SELECT name, stations FROM [{0}] WHERE  type=@type and extral=@extral", _tableName);
                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@type", type.ToString());
                param[1] = new SqlParameter("@extral", extral.ToString());

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, param);
                Dictionary<string, string> result = new Dictionary<string, string>();
                while (reader.Read())
                {
                    result[reader["name"].ToString()] = reader["stations"].ToString();
                }
                connection.Close();
                //CacheClient.Set(cacheKey, result);
                return result;
            //}
            //else
            //{
            //    Dictionary<string, string> result = cache as Dictionary<string, string>;
            //    return result;
            //}
        }
    }
}
